﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;
using PCSComUtils.DataAccess;
using PCSComUtils.PCSExc;
using PCSComUtils.Common;
using System.Runtime.InteropServices;

namespace PCSComProduction.WorkOrder.DS
{
    public class BaoCaoTienDoSanXuatMaHangDS
    {
        private const string THIS = "PCSComProduction.WorkOrder.DS.BaoCaoTienDoSanXuatMaHangDS";
        public DataTable GetReportData(int pstrSaleOrderDetailID)
        {
            const string METHOD_NAME = THIS + ".GetReportData()";
            DataSet dstPCS = new DataSet();



            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = ""
                    + " SELECT * FROM ((SELECT FGoodsCode, N'Chưa hoàn thành' StatusText, W.Status,"
                    + " P.Code PartNo, P.Description PartName, W.OrderQuantity, SUM(ISNULL(R.LaborRunTime,0)) * W.OrderQuantity ProduceTime,"
                    + " PL.Name ProductionLine"
                    + " FROM PRO_WorkOrderDetail W JOIN SO_SaleOrderDetail S ON W.SaleOrderDetailID = S.SaleOrderDetailID"
                    + " JOIN ITM_Product P ON W.ProductID = P.ProductID"
                    + " JOIN PRO_WORouting R ON W.WorkOrderDetailID = R.WorkOrderDetailID"
                    + " LEFT JOIN PRO_ProductionLine PL ON W.ProductionLineID = PL.ProductionLineID"
                    + " WHERE W.Status = 2"
                    + " AND W.SaleOrderDetailID = " + pstrSaleOrderDetailID
                    + " AND W.WorkOrderDetailID NOT IN (SELECT DISTINCT WorkOrderDetailID FROM PRO_WorkOrderCompletion)"
                    + " GROUP BY FGoodsCode, P.Code, P.Description, W.OrderQuantity, PL.Name, W.Status) "
                    + " UNION ALL"
                    + " (SELECT FGoodsCode, N'Hoàn thành' StatusText, W.Status,"
                    + " NULL PartNo, NULL PartName,"
                    + " SUM(W.OrderQuantity) OrderQuantity, R1.ProduceTime,"
                    + " NULL ProductionLine"
                    + " FROM PRO_WorkOrderDetail W JOIN SO_SaleOrderDetail S ON W.SaleOrderDetailID = S.SaleOrderDetailID"
                    + " JOIN ITM_Product P ON W.ProductID = P.ProductID"
                    + " JOIN (SELECT SUM(ISNULL(LaborRunTime,0) * D.OrderQuantity) ProduceTime, D.SaleOrderDetailID"
                    + " FROM PRO_WORouting R JOIN PRO_WorkOrderDetail D ON R.WorkOrderDetailID = D.WorkOrderDetailID"
                    + " JOIN SO_SaleOrderDetail SD ON D.SaleOrderDetailID = SD.SaleOrderDetailID"
                    + " AND SD.SaleOrderDetailID = " + pstrSaleOrderDetailID
                    + " WHERE D.WorkOrderDetailID IN (SELECT DISTINCT WorkOrderDetailID FROM PRO_WorkOrderCompletion)"
                    + " GROUP BY D.SaleOrderDetailID) R1"
                    + " ON W.SaleOrderDetailID = R1.SaleOrderDetailID"
                    + " WHERE W.Status = 3"
                    + " AND W.SaleOrderDetailID = " + pstrSaleOrderDetailID
                    + " AND W.WorkOrderDetailID IN (SELECT DISTINCT WorkOrderDetailID FROM PRO_WorkOrderCompletion)"
                    + " GROUP BY FGoodsCode, W.Status, R1.ProduceTime)) A"
                    + " ORDER BY A.ProductionLine";
               

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PRO_CompletionStdCostTable.TABLE_NAME);

                return dstPCS.Tables[0];
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }


        }

        public DataTable GetReportData(int pstrSaleOrderDetailID,int  pstrProductionLineID)
        {
            const string METHOD_NAME = THIS + ".GetReportData()";
            DataSet dstPCS = new DataSet();



            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = ""
                    + " SELECT * FROM ((SELECT FGoodsCode, N'Chưa hoàn thành' StatusText, W.Status,"
					+ " P.Code PartNo, P.Description PartName, W.OrderQuantity, SUM(ISNULL(R.LaborRunTime,0)) * W.OrderQuantity ProduceTime,"
					+ " PL.Name ProductionLine"
					+ " FROM PRO_WorkOrderDetail W JOIN SO_SaleOrderDetail S ON W.SaleOrderDetailID = S.SaleOrderDetailID"
					+ " JOIN ITM_Product P ON W.ProductID = P.ProductID"
					+ " JOIN PRO_WORouting R ON W.WorkOrderDetailID = R.WorkOrderDetailID"
					+ " LEFT JOIN PRO_ProductionLine PL ON W.ProductionLineID = PL.ProductionLineID"
					+ " WHERE W.Status = 2"
					+ " AND W.SaleOrderDetailID = " + pstrSaleOrderDetailID
					+ " AND W.ProductionLineID = " + pstrProductionLineID
					+ " AND W.WorkOrderDetailID NOT IN (SELECT DISTINCT WorkOrderDetailID FROM PRO_WorkOrderCompletion)"
					+ " GROUP BY FGoodsCode, P.Code, P.Description, W.OrderQuantity, PL.Name, W.Status) "
					+ " UNION ALL"
					+ " (SELECT FGoodsCode, N'Hoàn thành' StatusText, W.Status,"
					+ " NULL PartNo, NULL PartName,"
					+ " SUM(W.OrderQuantity) OrderQuantity, R1.ProduceTime,"
					+ " NULL ProductionLine"
					+ " FROM PRO_WorkOrderDetail W JOIN SO_SaleOrderDetail S ON W.SaleOrderDetailID = S.SaleOrderDetailID"
					+ " JOIN ITM_Product P ON W.ProductID = P.ProductID"
					+ " JOIN (SELECT SUM(ISNULL(LaborRunTime,0) * D.OrderQuantity) ProduceTime, D.SaleOrderDetailID"
					+ " FROM PRO_WORouting R JOIN PRO_WorkOrderDetail D ON R.WorkOrderDetailID = D.WorkOrderDetailID"
					+ " JOIN SO_SaleOrderDetail SD ON D.SaleOrderDetailID = SD.SaleOrderDetailID"
					+ " AND SD.SaleOrderDetailID = " + pstrSaleOrderDetailID
					+ " WHERE D.ProductionLineID = " + pstrProductionLineID
					+ " AND D.WorkOrderDetailID IN (SELECT DISTINCT WorkOrderDetailID FROM PRO_WorkOrderCompletion)"
					+ " GROUP BY D.SaleOrderDetailID) R1"
					+ " ON W.SaleOrderDetailID = R1.SaleOrderDetailID"
					+ " WHERE W.Status = 3"
					+ " AND W.SaleOrderDetailID = " + pstrSaleOrderDetailID
					+ " AND W.ProductionLineID = " + pstrProductionLineID
					+ " AND W.WorkOrderDetailID IN (SELECT DISTINCT WorkOrderDetailID FROM PRO_WorkOrderCompletion)"
					+ " GROUP BY FGoodsCode, W.Status, R1.ProduceTime)) A";


                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PRO_CompletionStdCostTable.TABLE_NAME);

                return dstPCS.Tables[0];
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }


        }
    }
}
